//DevNETCoder
//CopyRight By DevNET Group
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using STT.Entities;
using STT.DataAccess;
 
namespace STT.Business
{
    public class BTbGiangVien
    {
        public static DataTable SelectAll_Xem()
        {
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbGiangVien_SelectAll_Xem", null);
            return all;
        }
        public static DataTable SelectAllForCV()
        {
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbGiangVien_SelectAllForCV", null);
            return all;
        }
        public static DataTable SelectAllForDDL()
        {
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbGiangVien_SelectAllByAdmin", null);
            return all;
        }  
        //----------------------------------------//
        public static void UpdateTNGV(ETbGiangVien GiangVien)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaGV", GiangVien.MaGV);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbChat_UpdateTNGV", pr);
        }
        public static DataTable SelectAll_Xuat(int malop)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaLop", malop);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_SelectAll_Xuat", pr);
            return all;
        }
        //-----------------------------------------/
        public static DataTable GetDiemByMonHoc(string Mamonhoc, string Malop)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"Mamh", Convert.ToInt32(Mamonhoc));
            pr[1] = new SqlParameter(@"Malop", Convert.ToInt32(Malop));
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "[TbDiemHV_ByMaMH]", pr);
            return all;
        }
        public static void UpdateTB()
        {
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbGiangVien_UpdateTB", null);
        }
//-----------------------------------------------------------//
        public static DataTable SelectAll()
        {
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbGiangVien_SelectAll", null);
            return all;
        }
        public static DataTable SelectAllImport()
        {
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbGiangVien_SelectAllImport", null);
            return all;
        }
        
        public static DataTable SelectTop(int Top)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"Top", Top);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbGiangVien_SelectTop", pr);
            return all;
        }
        public static string TaoMaGV()
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"ReturnValue", SqlDbType.NChar);
            pr[0].Size = 8;
            pr[0].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbGiangVien_TaoMaGV", pr);
            
            return pr[0].Value.ToString();
        }

        public static DataTable GiangVien_LayDonVi(ETbGiangVien GiangVien)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaGV", GiangVien.MaGV);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbGiangVien_LayDonVi", pr);
            return all;
        }
        //-----------------------------------------------------------//
        public static void UpdateTBGV(ETbGiangVien OTbGiangVien)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaGV", OTbGiangVien.MaGV);
            pr[1] = new SqlParameter(@"TrangThaiTB", OTbGiangVien.TrangThaiTB);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbGiangVien_UpdateSoTB", pr);
        }
        //-----------------------------------------------------------//
        public static bool KiemTraDuLieu(ETbGiangVien GV)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter(@"MaDonVi", GV.MaDonVi);
            pr[1] = new SqlParameter(@"MaChucVu", GV.MaChucVu);
            pr[2] = new SqlParameter(@"MaGV", GV.MaGV);
            

            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbGiangVien_KiemTraDuLieu", pr);
            if (all.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        //-----------------------------------------------------------//
        public static bool KiemTraDuLieu1(ETbGiangVien GV)
        {
            SqlParameter[] pr = new SqlParameter[4];
            pr[0] = new SqlParameter(@"MaGV", GV.MaGV);
            pr[1] = new SqlParameter(@"MaDonVi", GV.MaDonVi);
            pr[2] = new SqlParameter(@"MaChucVu", GV.MaChucVu);
            pr[3] = new SqlParameter(@"SoCMT", GV.SoCMT);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbGiangVien_KiemTraDuLieu1", pr);
            if (all.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
//-----------------------------------------------------------//
        public static void Insert(ETbGiangVien OTbGiangVien)
        {
            SqlParameter[] pr = new SqlParameter[13];
            pr[0] = new SqlParameter(@"MaGV", OTbGiangVien.MaGV);
            pr[1] = new SqlParameter(@"TenGV", OTbGiangVien.TenGV);
            pr[2] = new SqlParameter(@"Sdt", OTbGiangVien.Sdt);
            pr[3] = new SqlParameter(@"DiaChi", OTbGiangVien.DiaChi);
            pr[4] = new SqlParameter(@"MaDonVi", OTbGiangVien.MaDonVi);
            pr[5] = new SqlParameter(@"MaChucVu", OTbGiangVien.MaChucVu);
            pr[6] = new SqlParameter(@"SoCMT", OTbGiangVien.SoCMT);
            pr[7] = new SqlParameter(@"MaTDChuyenMon", OTbGiangVien.MaTDChuyenMon);
            pr[8] = new SqlParameter(@"MaTDHocVan", OTbGiangVien.MaTDHocVan);
            pr[9] = new SqlParameter(@"GhiChu", OTbGiangVien.GhiChu);
            pr[10] = new SqlParameter(@"Anh", OTbGiangVien.Anh);
            pr[11] = new SqlParameter(@"NguoiTao", OTbGiangVien.NguoiTao);
            pr[12] = new SqlParameter(@"TrangThaiTB", OTbGiangVien.TrangThaiTB);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure,"TbGiangVien_Insert", pr);
        }

        //-----------------------------------------------------------//
        public static DataTable SearchTenGiangVien(ETbGiangVien GV, string TenDN)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"TenGV", GV.TenGV);
            pr[1] = new SqlParameter(@"TenDN", TenDN);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbGiangVien_Search", pr);
            return all;
        }

//-----------------------------------------------------------//
        public static void Update(ETbGiangVien OTbGiangVien)
        {
            SqlParameter[] pr = new SqlParameter[11];
            pr[0] = new SqlParameter(@"MaGV", OTbGiangVien.MaGV);
            pr[1] = new SqlParameter(@"TenGV", OTbGiangVien.TenGV);
            pr[2] = new SqlParameter(@"Sdt", OTbGiangVien.Sdt);
            pr[3] = new SqlParameter(@"DiaChi", OTbGiangVien.DiaChi);
            pr[4] = new SqlParameter(@"MaDonVi", OTbGiangVien.MaDonVi);
            pr[5] = new SqlParameter(@"MaChucVu", OTbGiangVien.MaChucVu);
            pr[6] = new SqlParameter(@"SoCMT", OTbGiangVien.SoCMT);
            pr[7] = new SqlParameter(@"MaTDChuyenMon", OTbGiangVien.MaTDChuyenMon);
            pr[8] = new SqlParameter(@"MaTDHocVan", OTbGiangVien.MaTDHocVan);
            pr[9] = new SqlParameter(@"GhiChu", OTbGiangVien.GhiChu);
            pr[10] = new SqlParameter(@"Anh", OTbGiangVien.Anh);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure,"TbGiangVien_Update", pr);
        }
//-----------------------------------------------------------//
        public static void Delete(string MaGV)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaGV",MaGV);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbGiangVien_Delete", pr);
        }
//-----------------------------------------------------------//
        public static ETbGiangVien SelectByIDGV(string MaGV)
        {
            ETbGiangVien OTbGiangVien = new ETbGiangVien();
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaGV", MaGV);
            IDataReader idr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "TbGiangVien_SelectByIDGV", pr);            
            if (idr.Read())
                OTbGiangVien = GetOneTbGiangVien(idr);
            idr.Close();
            idr.Dispose();
            return OTbGiangVien;            
        }

        public static DataTable SelectTeachersBySearch(ETbGiangVien GiangVien, int IdCourses, int Number, int IdLanguage, int IdClass,string TenDN)
        {
            SqlParameter[] pr = new SqlParameter[11];
            pr[0] = new SqlParameter(@"Id", GiangVien.MaGV == "" ? (object)DBNull.Value : GiangVien.MaGV.ToString());
            pr[1] = new SqlParameter(@"Name", GiangVien.TenGV == "" ? (object)DBNull.Value : GiangVien.TenGV);
            pr[2] = new SqlParameter(@"IdRegency", GiangVien.MaChucVu == -1 ? (object)DBNull.Value : GiangVien.MaChucVu.ToString());
            pr[3] = new SqlParameter(@"IdUnit", GiangVien.MaDonVi == -1 ? (object)DBNull.Value : GiangVien.MaDonVi.ToString());
            pr[4] = new SqlParameter(@"IdQualification", GiangVien.MaTDChuyenMon == -1 ? (object)DBNull.Value : GiangVien.MaTDChuyenMon.ToString());
            pr[5] = new SqlParameter(@"Literacy", GiangVien.MaTDHocVan == -1 ? (object)DBNull.Value : GiangVien.MaTDHocVan.ToString());
            pr[6] = new SqlParameter(@"Number", Number == -1 ? (object)DBNull.Value : Number.ToString());
            pr[7] = new SqlParameter(@"IdCourses", IdCourses == -1 ? (object)DBNull.Value : IdCourses.ToString());
            pr[8] = new SqlParameter(@"IdLanguage", IdLanguage == -1 ? (object)DBNull.Value : IdLanguage.ToString());
            pr[9] = new SqlParameter(@"IdClass", IdClass == -1 ? (object)DBNull.Value : IdClass.ToString());
            pr[10] = new SqlParameter(@"TenDN", TenDN);

            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "SelectTeachersBySearch", pr);
            return all;
        }

        public static DataTable StatisticsTeacherDaDay(ETbGiangVien GiangVien,string TenDN)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"IdUnit", GiangVien.MaDonVi == -1 ? (object)DBNull.Value : GiangVien.MaDonVi.ToString());
            pr[1] = new SqlParameter(@"TenDN", TenDN);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "StatisticsTeacherDaDay", pr);
            return all;
        }

        public static DataTable StatisticsTeacherChuaDay(ETbGiangVien GiangVien, string TenDN)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"IdUnit", GiangVien.MaDonVi == -1 ? (object)DBNull.Value : GiangVien.MaDonVi.ToString());
            pr[1] = new SqlParameter(@"TenDN", TenDN);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "StatisticsTeacherChuaDay", pr);
            return all;
        }

        public static DataSet SelectByID(string MaGV)
        {
            ETbGiangVien OTbGiangVien = new ETbGiangVien();
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaGV", MaGV);

            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbGiangVien_SelectByID", pr);
            DataSet ds = new DataSet();
            all.TableName = "Row";
            ds.Tables.Add(all);
            return ds;
        }


        public static void UpdateInfoGV(ETbGiangVien OTbGiangVien)
        {
            SqlParameter[] pr = new SqlParameter[6];
            pr[0] = new SqlParameter(@"MaGV", OTbGiangVien.MaGV);
            pr[1] = new SqlParameter(@"TenGV", OTbGiangVien.TenGV);
            pr[2] = new SqlParameter(@"Sdt", OTbGiangVien.Sdt);
            pr[3] = new SqlParameter(@"DiaChi", OTbGiangVien.DiaChi);
            pr[4] = new SqlParameter(@"GhiChu", OTbGiangVien.GhiChu);
            pr[5] = new SqlParameter(@"Anh", OTbGiangVien.Anh);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbGiangVien_Update2", pr);
        }
//-----------------------------------------------------------//
        private static ETbGiangVien GetOneTbGiangVien(IDataReader idr)
       {
            ETbGiangVien OTbGiangVien = new ETbGiangVien();
                if (idr["MaGV"] != DBNull.Value)
                    OTbGiangVien.MaGV = (string)idr["MaGV"];
                if (idr["TenGV"] != DBNull.Value)
                    OTbGiangVien.TenGV = (string)idr["TenGV"];
                if (idr["Sdt"] != DBNull.Value)
                    OTbGiangVien.Sdt = (string)idr["Sdt"];
                if (idr["DiaChi"] != DBNull.Value)
                    OTbGiangVien.DiaChi = (string)idr["DiaChi"];
                if (idr["MaDonVi"] != DBNull.Value)
                    OTbGiangVien.MaDonVi = (int)idr["MaDonVi"];
                if (idr["MaChucVu"] != DBNull.Value)
                    OTbGiangVien.MaChucVu = (int)idr["MaChucVu"];
                if (idr["SoCMT"] != DBNull.Value)
                    OTbGiangVien.SoCMT = (string)idr["SoCMT"];
                if (idr["MaTDChuyenMon"] != DBNull.Value)
                    OTbGiangVien.MaTDChuyenMon = (int)idr["MaTDChuyenMon"];
                if (idr["MaTDHocVan"] != DBNull.Value)
                    OTbGiangVien.MaTDHocVan = (int)idr["MaTDHocVan"];
                if (idr["GhiChu"] != DBNull.Value)
                    OTbGiangVien.GhiChu = (string)idr["GhiChu"];
                if (idr["Anh"] != DBNull.Value)
                    OTbGiangVien.Anh = (string)idr["Anh"];
            return OTbGiangVien;
       }
//-----------------------------------------------------------//
        public static bool TestByID(int MaGV)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaGV",MaGV);
            pr[1] = new SqlParameter(@"TestID",SqlDbType.Bit);
            pr[1].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure,"TbGiangVien_TestByID", pr);
            return Convert.ToBoolean(pr[1].Value);
        }
//-----------------------------------------------------------//
        public static List<ETbGiangVien> ListAll()
        {
            List<ETbGiangVien> list = new List<ETbGiangVien>();
            IDataReader idr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "TbGiangVien_SelectAll", null);
            while (idr.Read())
              list.Add(GetOneTbGiangVien(idr));
            if (idr.IsClosed == false)
            {
               idr.Close();
               idr.Dispose();
            }
            return list;
        }

        public static List<ETbGiangVien> ListTop(int Top)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"Top", Top);
            List<ETbGiangVien> list = new List<ETbGiangVien>();
            IDataReader idr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "TbGiangVien_SelectAll", pr);
            while (idr.Read())
              list.Add(GetOneTbGiangVien(idr));
            if (idr.IsClosed == false)
            {
               idr.Close();
               idr.Dispose();
            }
            return list;
        }
    }
 
}